﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	/// <summary>
	/// Summary description for Baocao_chamcong.
	/// </summary>
	public class Baocao_ngayphep : System.Windows.Forms.Form
	{
        private AccessData mDB = new AccessData();
        Language lan = new Language();
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		private System.Windows.Forms.TextBox txtthang;
		private System.Windows.Forms.TextBox txtnam;
		private string thang="",nam="",s_mmyy="",s_bophan="",user_dn="";
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.ComboBox cbobophan;
        private Button btnexel;
		private System.ComponentModel.Container components = null;

		public Baocao_ngayphep(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Baocao_ngayphep));
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.button1 = new System.Windows.Forms.Button();
            this.button2 = new System.Windows.Forms.Button();
            this.txtthang = new System.Windows.Forms.TextBox();
            this.txtnam = new System.Windows.Forms.TextBox();
            this.label3 = new System.Windows.Forms.Label();
            this.cbobophan = new System.Windows.Forms.ComboBox();
            this.btnexel = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Tahoma", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.ForeColor = System.Drawing.Color.DarkBlue;
            this.label1.Location = new System.Drawing.Point(15, 14);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(56, 23);
            this.label1.TabIndex = 4;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Tahoma", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.ForeColor = System.Drawing.Color.DarkBlue;
            this.label2.Location = new System.Drawing.Point(135, 17);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(40, 23);
            this.label2.TabIndex = 5;
            this.label2.Text = "Năm :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // button1
            // 
            this.button1.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.button1.ForeColor = System.Drawing.Color.DarkBlue;
            this.button1.Image = global::Human.Properties.Resources.t_search;
            this.button1.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.button1.Location = new System.Drawing.Point(33, 91);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(60, 28);
            this.button1.TabIndex = 3;
            this.button1.Text = "Chọn";
            this.button1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // button2
            // 
            this.button2.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.button2.ForeColor = System.Drawing.Color.DarkBlue;
            this.button2.Image = global::Human.Properties.Resources.close_r;
            this.button2.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.button2.Location = new System.Drawing.Point(164, 91);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(75, 28);
            this.button2.TabIndex = 5;
            this.button2.Text = "Kết thúc";
            this.button2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // txtthang
            // 
            this.txtthang.Location = new System.Drawing.Point(77, 17);
            this.txtthang.Name = "txtthang";
            this.txtthang.Size = new System.Drawing.Size(40, 21);
            this.txtthang.TabIndex = 0;
            this.txtthang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtthang_KeyDown);
            // 
            // txtnam
            // 
            this.txtnam.Location = new System.Drawing.Point(177, 16);
            this.txtnam.Name = "txtnam";
            this.txtnam.Size = new System.Drawing.Size(56, 21);
            this.txtnam.TabIndex = 1;
            this.txtnam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtnam_KeyDown);
            // 
            // label3
            // 
            this.label3.Font = new System.Drawing.Font("Tahoma", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.DarkBlue;
            this.label3.Location = new System.Drawing.Point(15, 53);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(56, 23);
            this.label3.TabIndex = 6;
            this.label3.Text = "Bộ phận :";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // cbobophan
            // 
            this.cbobophan.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cbobophan.ForeColor = System.Drawing.Color.Navy;
            this.cbobophan.Location = new System.Drawing.Point(77, 52);
            this.cbobophan.Name = "cbobophan";
            this.cbobophan.Size = new System.Drawing.Size(162, 22);
            this.cbobophan.TabIndex = 2;
            this.cbobophan.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cbobophan_KeyDown);
            // 
            // btnexel
            // 
            this.btnexel.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btnexel.ForeColor = System.Drawing.Color.DarkBlue;
            this.btnexel.Image = global::Human.Properties.Resources.white_excel;
            this.btnexel.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btnexel.Location = new System.Drawing.Point(99, 91);
            this.btnexel.Name = "btnexel";
            this.btnexel.Size = new System.Drawing.Size(60, 28);
            this.btnexel.TabIndex = 4;
            this.btnexel.Text = "Excel";
            this.btnexel.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btnexel.Click += new System.EventHandler(this.btnexel_Click);
            // 
            // Baocao_ngayphep
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 14);
            this.ClientSize = new System.Drawing.Size(261, 135);
            this.Controls.Add(this.btnexel);
            this.Controls.Add(this.cbobophan);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.txtnam);
            this.Controls.Add(this.txtthang);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.ForeColor = System.Drawing.Color.Navy;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "Baocao_ngayphep";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "BC ngày phép";
            this.Load += new System.EventHandler(this.Baocao_chamcong_Load);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void txtthang_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

		private void txtnam_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

		private void Baocao_chamcong_Load(object sender, System.EventArgs e)
		{
			try
			{
				txtthang.Text =DateTime.Now.Month.ToString();
				txtnam.Text =DateTime.Now.Year.ToString();
				thang =txtthang.Text.Trim().PadLeft(2,'0');
				nam =txtnam.Text.Trim();
				Load_bophan();
			}
			catch{}
        }
		private void Load_bophan()
		{
			cbobophan.DisplayMember="tenbp";
			cbobophan.ValueMember="madv";
			cbobophan.DataSource =mDB.get_bophan().Tables[0];
			cbobophan.SelectedIndex =-1;
		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			if(!ktra(txtthang.Text.Trim()))
			{
				MessageBox.Show(lan.Change_language_MessageText("Tháng không hợp lệ !"),lan.Change_language_MessageText("Thông báo"));
				txtthang.Focus();
				return;
			}
			if(!ktra(txtnam.Text.Trim()))
			{
				MessageBox.Show(lan.Change_language_MessageText("Năm không hợp lệ !"),lan.Change_language_MessageText("Thông báo"));
				txtnam.Focus();
				return;
			}
			thang =txtthang.Text.Trim().PadLeft(2,'0');
			nam =txtnam.Text.Trim();
			if(!mDB.bMmyy(thang+nam.Substring(2,2)))
			{
				MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo !"),lan.Change_language_MessageText("Thông báo"));
				return;
			}
            print(1);
			button2.Focus();
		}

		private void button2_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private bool ktra(string a)
		{
			int i=0;
			try
			{
				i =int.Parse(a.Trim());
				return true;
			}
			catch
			{
				return false;
			}
		}
		
		private void print(int prn)
		{
            DataSet dstmp = new DataSet();
            ExportToExcel exp = new ExportToExcel();

            string sql="",m_user="";
            m_user = mDB.user;
			DataSet ds =new DataSet();
			sql =" select '"+thang+"' as thang, '"+nam+"' as nam,hd.macv,l.manv,bp.madv,bp.tenbp as bophan,trim(l.ho)||' '||trim(l.ten) as hoten,cd.ten,";
			sql += " to_char(hd.ngayphepnam,'dd/mm/yyyy') as ngayvaolam,b.phep as phepnam,b.phepthem as phepthem";
            sql += " ,t1 as t1,t2 as t2,t3 as t3,t4 as t4,t5 as t5,t6 as t6,t7 as t7,t8 as t8,t9 as t9,t10 as t10,t11 as t11,t12 as t12";
			sql += " ,(t1+t2+t3+t4+t5+t6+t7+t8+t9+t10+t11+t12) as tongnghi,bp.sothutu as stt_bp,cd.thutu as stt_cd,cv.stt as stt_cv";
            sql += " from " + m_user + ".llnv as l inner join " + m_user + ".hopdong as hd on l.manv=hd.manv";
            sql += " inner join " + m_user + ".phepnam as b on l.manv=b.manv ";
            sql += " left join " + m_user + ".dmcd as cd on l.macd=cd.ma";
            sql += " inner join " + m_user + ".bophan as bp on bp.madv=b.madv ";
            sql += " inner join " + m_user + ".dmcv as cv on hd.macv=cv.macv ";
            sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
			sql += " where (hd.ngaynghi is null or (hd.ngaynghi is not null and hd.ngaynghi>to_date('31/12/"+nam+"','dd/mm/yyyy'))) ";
            sql += " and b.nam='" + nam + "'";
			if(cbobophan.SelectedIndex>-1)
				sql+=" and b.madv='"+cbobophan.SelectedValue.ToString().Trim()+"'";
            sql += " and hd.lan=ml.lan";
            sql += " order by bp.sothutu,cv.stt,cd.thutu ";
			try
			{
				ds =mDB.get_data(sql);
                ds.WriteXml("..\\xml\\h_bcngaypheptrongnam.xml", XmlWriteMode.WriteSchema);
                s_mmyy = txtthang.Text.Trim().PadLeft(2, '0') + txtnam.Text.Trim().Substring(2, 2);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (cbobophan.SelectedIndex > -1)
                    {
                        switch (prn)
                        {
                            case 1: frmReport f = new frmReport(mDB, ds, "", "h_bcngaypheptrongnam.rpt", true, 0,user_dn);
                                f.ShowDialog();
                                break;
                            case 2: dstmp = exp_exel(ds);
                                exp.exp_excel_bcnghiphep(dstmp, "bao cao nghi phep", txtthang.Text, txtnam.Text,s_mmyy);
                                break;
                        }                        
                    }
                    else
                    {
                        frmReport f = new frmReport(mDB, ds, "", "h_bcngaypheptrongnam.rpt", true, 0,user_dn);
                        f.ShowDialog();
                    }
                }
                else
                    MessageBox.Show(lan.Change_language_MessageText("Không có dữ liệu về ngày phép trong tháng ")+txtthang.Text.Trim()+"/"+txtnam.Text.Trim(), "Chú ý");
			}
			catch{}
		}
        private DataSet exp_exel(DataSet dse)
        {
            string[] str = new string[] {lan.Change_language_MessageText( "STT"),lan.Change_language_MessageText( "Họ Và Tên"),lan.Change_language_MessageText( "Chức Danh"),lan.Change_language_MessageText( "Ngày Vào"),lan.Change_language_MessageText( "PH Trong Năm"),lan.Change_language_MessageText( "PH Thêm"), "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", lan.Change_language_MessageText("TC Nghỉ Phép"),lan.Change_language_MessageText( "Phép Còn"),lan.Change_language_MessageText( "Ghi Chú") };
            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("bophan", typeof(string));
            dse1.Tables[0].Columns.Add("ngayvaolam", typeof(string));
            dse1.Tables[0].Columns.Add("phepnam", typeof(string));
            dse1.Tables[0].Columns.Add("phepthem", typeof(string));
            dse1.Tables[0].Columns.Add("t1", typeof(string));
            dse1.Tables[0].Columns.Add("t2", typeof(string));
            dse1.Tables[0].Columns.Add("t3", typeof(string));
            dse1.Tables[0].Columns.Add("t4", typeof(string));
            dse1.Tables[0].Columns.Add("t5", typeof(string));
            dse1.Tables[0].Columns.Add("t6", typeof(string));
            dse1.Tables[0].Columns.Add("t7", typeof(string));
            dse1.Tables[0].Columns.Add("t8", typeof(string));
            dse1.Tables[0].Columns.Add("t9", typeof(string));
            dse1.Tables[0].Columns.Add("t10", typeof(string));
            dse1.Tables[0].Columns.Add("t11", typeof(string));
            dse1.Tables[0].Columns.Add("t12", typeof(string));          
            dse1.Tables[0].Columns.Add("tongnghi", typeof(string));                    

            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt;
                nrow["hoten"] = r["hoten"].ToString();
                nrow["bophan"] = r["bophan"].ToString();
                nrow["ngayvaolam"] = r["ngayvaolam"].ToString();
                nrow["phepnam"] = r["phepnam"].ToString();
                nrow["phepthem"] = r["phepthem"].ToString();
                nrow["t1"] = r["t1"].ToString();
                nrow["t2"] = r["t2"].ToString();
                nrow["t3"] = r["t3"].ToString();
                nrow["t4"] = r["t4"].ToString();
                nrow["t5"] = r["t5"].ToString();
                nrow["t6"] = r["t6"].ToString();
                nrow["t7"] = r["t7"].ToString();
                nrow["t8"] = r["t8"].ToString();
                nrow["t9"] = r["t9"].ToString();
                nrow["t10"] = r["t10"].ToString();
                nrow["t11"] = r["t11"].ToString();
                nrow["t12"] = r["t12"].ToString();                
                nrow["tongnghi"] = r["tongnghi"].ToString();                    
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }

        private void btnexel_Click(object sender, EventArgs e)
        {
            print(2);
        }

        private void cbobophan_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
	}
}
